package dao.impl;

import dao.TeamDao;
import model.JoinTeamMsg2;
import model.Race;
import model.Team;
import service.UserService;
import service.impl.UserServiceImpl;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * The type Team dao.
 *
 * @author yzh
 * @create 2022 -12-12 11:25
 */
public class TeamDaoImpl implements TeamDao {
    /**
     * The Pstmt.
     */
    PreparedStatement pstmt = null;
    /**
     * The Rs.
     */
    ResultSet rs = null;
    @Override
    public int insert(Team team) {
        int tid = 0;
        String sql = "insert into team (tname,lid,rid,turl,smid,pass) values (?,?,?,?,?,?)";
        Connection conn = BaseDao.getConnection();
        try {
            pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            pstmt.setString(1, team.getTname());
            pstmt.setLong(2, team.getLid());
            pstmt.setLong(3, team.getRid());
            pstmt.setString(4, team.getTurl());
            pstmt.setLong(5, team.getSmid());
            pstmt.setLong(6, team.getPass());
            pstmt.executeUpdate();

            rs = pstmt.getGeneratedKeys();
            if(rs.next()) {
                tid = rs.getInt(1);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            BaseDao.close(null, pstmt, conn);
        }
        return tid;
    }

    @Override
    public int delete(int id) {
        int count = 0;
        String sql = "DELETE FROM team WHERE tid= ?";
        Connection conn = BaseDao.getConnection();
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);

            count = pstmt.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            BaseDao.close(null, pstmt, conn);
        }
        return count;
    }

    @Override
    public Team findById(int id) {
        Team team = null;
        String sql = "SELECT * FROM team WHERE tid = ?";
        Connection conn = BaseDao.getConnection();
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                team = new Team();
                team.setTid(id);
                team.setTname(rs.getString("tname"));
                team.setLid(rs.getInt("lid"));
                team.setRid(rs.getInt("rid"));
                team.setTurl(rs.getString("turl"));
                team.setSmid(rs.getInt("smid"));
                team.setPass(rs.getInt("pass"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            BaseDao.close(rs, pstmt, conn);
        }
        return team;
    }

    @Override
    public List<Team> findAll(int pageIndex, int pageSize) {
        List<Team> list = new ArrayList<>();
        String sql = "SELECT * FROM team LIMIT ?,?";
        Connection conn = BaseDao.getConnection();
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, (pageIndex - 1) * pageSize);
            pstmt.setInt(2, pageSize);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                Team team = new Team();
                team.setTid(rs.getInt("tid"));
                team.setTname(rs.getString("tname"));
                team.setLid(rs.getInt("lid"));
                team.setRid(rs.getInt("rid"));
                team.setTurl(rs.getString("turl"));
                team.setSmid(rs.getInt("smid"));
                team.setPass(rs.getInt("pass"));

                list.add(team);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            BaseDao.close(rs, pstmt, conn);
        }
        return list;
    }

    @Override
    public int pageCount(int tid) {
        int count = 0;
        String sql = "SELECT count(uid) from team_user  where tid =? ";
        Connection conn = BaseDao.getConnection();
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, tid);

            final ResultSet resultSet = pstmt.executeQuery();
            while (resultSet.next()) {
                count = resultSet.getInt(1);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            BaseDao.close(rs, pstmt, conn);
        }
        System.out.println(count);
        return count;
    }

    @Override
    public int teamUserAdd(int uid, int tid) {
        int count = 0;
        String sql = "insert into team_user (uid,tid) values (?,?)";
        Connection conn = BaseDao.getConnection();
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, uid);
            pstmt.setInt(2, tid);
            count = pstmt.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            BaseDao.close(null, pstmt, conn);
        }
        return count;
    }

    @Override
    public int teamDelete(int tid) {
        int count = 0;
        String sql = "DELETE FROM team_user WHERE  tid = ?";
        Connection conn = BaseDao.getConnection();
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, tid);

            count = pstmt.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            BaseDao.close(rs, pstmt, conn);
        }
        return count;
    }

    @Override
    public int teamQuite(int tid, int uid) {
        int count = 0;
        String sql = "DELETE FROM team_user WHERE  tid = ? and uid = ?";
        Connection conn = BaseDao.getConnection();
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, tid);
            pstmt.setInt(2, uid);
            count = pstmt.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            BaseDao.close(rs, pstmt, conn);
        }
        return count;
    }

    @Override
    public List<Team> findTeamListByRace(int rid, int pageIndex , int pageSize) {
        List<Team> list = new ArrayList<>();
        String sql = "SELECT * FROM team WHERE rid = ? LIMIT ?,?";
        Connection conn = BaseDao.getConnection();
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, rid);
            pstmt.setInt(2, (pageIndex - 1) * pageSize);
            pstmt.setInt(3, pageSize);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                Team team = new Team();
                team.setTid(rs.getInt("tid"));
                team.setTname(rs.getString("tname"));
                team.setLid(rs.getInt("lid"));
                team.setRid(rs.getInt("rid"));
                team.setTurl(rs.getString("turl"));
                team.setSmid(rs.getInt("smid"));
                team.setPass(rs.getInt("pass"));

                list.add(team);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            BaseDao.close(rs, pstmt, conn);
        }
        return list;
    }

    @Override
    public int teamListByRaceCount(int rid) {
        int count = 0;
        String sql = "SELECT COUNT(*) FROM team WHERE rid = ?";
        Connection conn = BaseDao.getConnection();
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, rid);
            final ResultSet resultSet = pstmt.executeQuery();
            while (resultSet.next()) {
                count = resultSet.getInt(1);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            BaseDao.close(rs, pstmt, conn);
        }
        return count;
    }


    @Override
    public List<Team> findByPartTname(String partTname, int pageIndex, int pageSize) {
        List<Team> list = new ArrayList<>();
        String sql = "SELECT * FROM team WHERE tname like ? LIMIT ?,?";
        Connection conn = BaseDao.getConnection();
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, "%" + partTname + "%");
            pstmt.setInt(2, (pageIndex - 1) * pageSize);
            pstmt.setInt(3, pageSize);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                Team team = new Team();
                team.setTid(rs.getInt("tid"));
                team.setTname(rs.getString("tname"));
                team.setLid(rs.getInt("lid"));
                team.setRid(rs.getInt("rid"));
                team.setTurl(rs.getString("turl"));
                team.setSmid(rs.getInt("smid"));
                team.setPass(rs.getInt("pass"));

                list.add(team);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            BaseDao.close(rs, pstmt, conn);
        }
        return list;
    }

    @Override
    public int getTeamUserCount(int tid) {
        int count = 0;
        String sql = "SELECT COUNT(*) FROM team_user WHERE tid = ?";
        Connection conn = BaseDao.getConnection();
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, tid);
            final ResultSet resultSet = pstmt.executeQuery();
            if (resultSet.next()) {
                count = resultSet.getInt(1);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            BaseDao.close(rs, pstmt, conn);
        }
        return count;
    }
    @Override
    public List<Team> teamByRaceAndName(int rid, String tname, int pageIndex, int pageSize) {
        List<Team> teamList = new ArrayList<Team>();
        String sql = "SELECT * FROM team where rid = ? and tname Like ? LIMIT ?,?";
        Connection conn = BaseDao.getConnection();
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, rid);
            pstmt.setString(2, "%" + tname + "%");
            pstmt.setInt(3, (pageIndex - 1) * pageSize);
            pstmt.setInt(4, pageSize);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                Team team = new Team();
                team.setTid(rs.getInt("tid"));
                team.setTname(rs.getString("tname"));
                team.setLid(rs.getInt("lid"));
                team.setRid(rs.getInt("rid"));
                team.setTurl(rs.getString("turl"));
                team.setSmid(rs.getInt("smid"));
                team.setPass(rs.getInt("pass"));

                teamList.add(team);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            BaseDao.close(rs, pstmt, conn);
        }
        return teamList;
    }

    @Override
    public int teamByRaceAndNameCount(int rid, String tname) {
        int count = 0;
        String sql = "SELECT COUNT(*) FROM team where rid = ? and tname Like ?";
        Connection conn = BaseDao.getConnection();
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, rid);
            pstmt.setString(2, "%" + tname + "%");
            final ResultSet resultSet = pstmt.executeQuery();
            while (resultSet.next()) {
                count = resultSet.getInt(1);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            BaseDao.close(rs, pstmt, conn);
        }
        return count;
    }


    @Override
    public boolean isInTeam(int uid, int tid, int rid) {
        int count = 0;

        String sql = "select rid from race where rid in (select rid from team where tid in (select tid from team_user where uid =?)) ";
        Connection conn = BaseDao.getConnection();
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, uid);

            final ResultSet rs = pstmt.executeQuery();

            while (rs.next()) {
                int tmp = rs.getInt(1);
                System.out.println(1);
                System.out.println(tmp);

                if (tmp == rid) {
                    return true;
                }
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            BaseDao.close(null, pstmt, conn);
        }
        return false;
    }

    @Override
    public boolean isInTeam2(int uid, int tid) {
        int count = 0;

        String sql = " select count(tid) from  team_user where uid =? and tid=?";
        Connection conn = BaseDao.getConnection();
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, uid);
            pstmt.setInt(2, tid);

            final ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                count = rs.getInt(1);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            BaseDao.close(null, pstmt, conn);
        }
        return count > 0;
    }


    @Override
    public int teamByIdAndRace(int lid, int rid) {
        int count = 0;
        String sql = "SELECT COUNT(*) FROM team where lid = ? and rid = ?";
        Connection conn = BaseDao.getConnection();
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, lid);
            pstmt.setInt(2, rid);
            final ResultSet resultSet = pstmt.executeQuery();
            if (resultSet.next()) {
                count = resultSet.getInt(1);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            BaseDao.close(rs, pstmt, conn);
        }
        return count;
    }

    @Override
    public Race raceByTid(int tid) {

        Connection conn = BaseDao.getConnection();
        String sql = "select * from race where rid in (select rid from team where tid =?) ";
        try {

            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, tid);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                Race race = new Race(rs.getString("rname"));
                return race;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
//            BaseDao.close(rs, pstmt, conn);//这里不能关闭,因为这里是一个子连接,上一级的查询还在继续,如果关闭就会抛异常.
        }
        return null;
    }

    @Override
    public int teamCountByUser(int uid) {
        int count = 0;
        System.out.println(uid);

        String sql = " select count(*) from team where tid in (select tid from team_user where uid =?)";
        Connection conn = BaseDao.getConnection();
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, uid);

            final ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                count = rs.getInt(1);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            BaseDao.close(null, pstmt, conn);
        }
        return count;
    }


    public List<Team> teamListByUser(int uid, int pageIndex, int pageSize) {
        UserService userServiceImpl = new UserServiceImpl();
        List<Team> list = new ArrayList<>();
        System.out.println(uid);
        System.out.println(pageIndex);
        System.out.println(pageSize);
        String sql = "select * from team where tid in (select tid from team_user where uid =?) limit ? offset ?";
        Connection conn = BaseDao.getConnection();
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, uid);
            pstmt.setInt(2, pageSize);
            pstmt.setInt(3, (pageIndex - 1) * pageSize);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                Team team = new Team();
                team.setTid(rs.getInt("tid"));
                team.setTname(rs.getString("tname"));
                team.setLid(rs.getInt("lid"));
                team.setTurl(rs.getString("turl"));
                team.setSmid(rs.getInt("smid"));
                team.setPass(rs.getInt("pass"));
                list.add(team);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            BaseDao.close(rs, pstmt, conn);//最后一级操作,关闭资源!!!
        }
        System.out.println(list.toString());
        return list;
    }
}
